import configparser
import os

import pymysql

config = configparser.ConfigParser()
directory = os.path.dirname(__file__)
path = os.path.join(directory, 'settings.ini')
config.read(path)


MYSQL_DB_NAME = config.get('MYSQL', 'database')
MYSQL_HOST = config.get('MYSQL', 'host')
MYSQL_PORT = int(config.get('MYSQL', 'port'))
MYSQL_USER = config.get('MYSQL', 'user')
MYSQL_PASSWORD = config.get('MYSQL', 'password')
MYSQL_TABLE = config.get('MYSQL', 'table')
MYSQL_ALL_SSQ_TABLE = config.get('MYSQL', 'all_ssq')

def readident(ident):
    db = MYSQL_DB_NAME
    host = MYSQL_HOST
    port = MYSQL_PORT
    user = MYSQL_USER
    password = MYSQL_PASSWORD
    table = MYSQL_TABLE

    config = {
        "host": host,
        "port": port,
        "database": db,
        "user": user,
        "password": password,
        "charset": 'utf8',
    }
    db_conn = pymysql.connect(**config)
    # db_cur = db_conn.cursor()
    with db_conn.cursor() as db_cur:
        sql = f"SELECT {ident} FROM {table}"
        effect_rows = db_cur.execute(sql)
        idents = db_cur.fetchall()
        db_cur.close()

    db_conn.close()

    return idents


def write_all():
    from itertools import combinations, product

    red_balls = range(1, 34)
    red_comb_balls = combinations(red_balls, 6)
    red_comb_balls_lst = list(red_comb_balls)
    db = MYSQL_DB_NAME
    host = MYSQL_HOST
    port = MYSQL_PORT
    user = MYSQL_USER
    password = MYSQL_PASSWORD
    table = MYSQL_ALL_SSQ_TABLE

    config = {
        "host": host,
        "port": port,
        "database": db,
        "user": user,
        "password": password,
        "charset": 'utf8',
    }
    db_conn = pymysql.connect(**config)
    # db_cur = db_conn.cursor()
    with db_conn.cursor() as db_cur:
        for index, balls in enumerate(red_comb_balls_lst, start=1):
            item = {f'red_{i+1}': ball for i, ball in enumerate(balls)}
            item.update({'id': index})
            sql = (f'INSERT INTO {table} (id, red_1, red_2, red_3, '
                'red_4, red_5, red_6) '
                'VALUES (%(id)s, %(red_1)s, '
                '%(red_2)s, %(red_3)s, %(red_4)s, %(red_5)s, '
                '%(red_6)s)')
            effect_rows = db_cur.execute(sql, item)
        # idents = db_cur.fetchall()
        db_cur.close()
    db_conn.commit()
    db_conn.close()
    return red_comb_balls_lst


# write_all()